<!DOCTYPE html>
<html>
<head hexo-theme='https://volantis.js.org/#2.6.6'>
  <meta charset="utf-8">
  <!-- SEO相关 -->
  
    
  
  <!-- 渲染优化 -->
  <meta name="renderer" content="webkit">
  <meta name="force-rendering" content="webkit">
  <meta http-equiv="X-UA-Compatible" content="IE=Edge,chrome=1">
  <meta name="HandheldFriendly" content="True" >
  <meta name="apple-mobile-web-app-capable" content="yes">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">

  <!-- 页面元数据 -->
  
    <title>数据库概论知识点总结 - Jun&#39;s Blog</title>
  
    <meta name="keywords" content="SQL,T-SQL,数据库">
  
  
    <meta name="description" content="数据库系统的基本概念
从数据到数据库
数据
数据的概念
数据：数据的内容是信息；数据的表现形式是符号记录
数据的分类

按数据加工程度分：分为三次数据、二次数据、一次数据、零次数据（加工程度由高到低）
按结构化程度分：




类型
含义
本质
举例




结构化数据...">
  

  <!-- feed -->
  
    <link rel="alternate" href="/junblog/atom.xml" title="Jun's Blog">
  

  <!-- import meta -->
  

  <!-- link -->
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.13/css/all.min.css">
  
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3.5.7/dist/jquery.fancybox.min.css">

  
  
    
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/node-waves@0.7.6/dist/waves.min.css">

  

  
  <link rel="shortcut icon" type='image/x-icon' href="https://cdn.jsdelivr.net/gh/xaoxuu/assets@master/favicon/favicon.ico">
  

  

  

  <!-- import link -->
  

  
  
    
<link rel="stylesheet" href="/junblog/css/style.css">

  

  <script>
    function setLoadingBarProgress(num) {
      document.getElementById('loading-bar').style.width=num+"%";
    }
  </script>

  
  
</head>

<body>
  
  <div id="loading-bar-wrapper">
  <div id="loading-bar"></div>
</div>
<header class="l_header shadow blur">
  <div class='container'>
  <div class='wrapper'>
    <div class='nav-sub'>
      <p class="title"></p>
      <ul class='switcher nav-list-h'>
        <li><a class="s-comment fas fa-comments fa-fw" target="_self" href='javascript:void(0)'></a></li>
        
          <li><a class="s-toc fas fa-list fa-fw" target="_self" href='javascript:void(0)'></a></li>
        
      </ul>
    </div>
		<div class="nav-main">
      
        
        <a class="title flat-box" target="_self" href='/junblog/'>
          
          
          
            Jun's Blog
          
          
        </a>
      

			<div class='menu navigation'>
				<ul class='nav-list-h'>
          
          
          
            
            
              <li>
                <a class="flat-box" href=/junblog/
                  
                  
                  
                    id="junblog"
                  >
                  <i class='fas fa-rss fa-fw'></i>首页
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/junblog/categories/
                  
                  
                  
                    id="junblogcategories"
                  >
                  <i class='fas fa-folder-open fa-fw'></i>分类
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/junblog/tags/
                  
                  
                  
                    id="junblogtags"
                  >
                  <i class='fas fa-tags fa-fw'></i>标签
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/junblog/archives/
                  
                  
                  
                    id="junblogarchives"
                  >
                  <i class='fas fa-archive fa-fw'></i>归档
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/junblog/friends/
                  
                  
                  
                    id="junblogfriends"
                  >
                  <i class='fas fa-link fa-fw'></i>友链
                </a>
                
              </li>
            
          
          
            
            
              <li>
                <a class="flat-box" href=/junblog/aboutme/
                  
                  
                  
                    id="junblogaboutme"
                  >
                  <i class='fas fa-info-circle fa-fw'></i>关于
                </a>
                
              </li>
            
          
          
				</ul>
			</div>

      <div class="m_search">
        <form name="searchform" class="form u-search-form">
          <i class="icon fas fa-search fa-fw"></i>
          <input type="text" class="input u-search-input" placeholder="Search..." />
        </form>
      </div>

			<ul class='switcher nav-list-h'>
				
					<li><a class="s-search fas fa-search fa-fw" target="_self" href='javascript:void(0)'></a></li>
				
				<li>
          <a class="s-menu fas fa-bars fa-fw" target="_self" href='javascript:void(0)'></a>
          <ul class="menu-phone list-v navigation white-box">
            
              
            
              <li>
                <a class="flat-box" href=/junblog/
                  
                  
                  
                    id="junblog"
                  >
                  <i class='fas fa-rss fa-fw'></i>首页
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/junblog/categories/
                  
                  
                  
                    id="junblogcategories"
                  >
                  <i class='fas fa-folder-open fa-fw'></i>分类
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/junblog/tags/
                  
                  
                  
                    id="junblogtags"
                  >
                  <i class='fas fa-tags fa-fw'></i>标签
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/junblog/archives/
                  
                  
                  
                    id="junblogarchives"
                  >
                  <i class='fas fa-archive fa-fw'></i>归档
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/junblog/friends/
                  
                  
                  
                    id="junblogfriends"
                  >
                  <i class='fas fa-link fa-fw'></i>友链
                </a>
                
              </li>
            
          
            
              
            
              <li>
                <a class="flat-box" href=/junblog/aboutme/
                  
                  
                  
                    id="junblogaboutme"
                  >
                  <i class='fas fa-info-circle fa-fw'></i>关于
                </a>
                
              </li>
            
          
            
          </ul>
        </li>
			</ul>
		</div>
	</div>
  </div>
</header>

<script>setLoadingBarProgress(40);</script>



  <div class="l_body nocover">
    <div class='body-wrapper'>
      

<div class='l_main'>
  

  
    <article id="post" class="post white-box reveal blur article-type-post" itemscope itemprop="blogPost">
      


  <section class='meta'>
    
      
      
      <div class="meta" id="header-meta">
        
          
  <h1 class="title">
    <a href="/junblog/2021/01/23/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%A6%82%E8%AE%BA%E5%A4%8D%E4%B9%A0/">
      数据库概论知识点总结
    </a>
  </h1>


        
        <div class='new-meta-box'>
          
            
          
            
              
<div class='new-meta-item author'>
  <a href="" rel="nofollow">
    <img src="https://cdn.jsdelivr.net/gh/JunyuanHe/cdn-assets@master/img/GeographicObjects2.jpg">
    <p>Jun</p>
  </a>
</div>

            
          
            
              
  
  <div class='new-meta-item category'>
    <a href='/junblog/categories/%E8%AE%A1%E7%AE%97%E6%9C%BA/%E6%95%B0%E6%8D%AE%E5%BA%93/' rel="nofollow">
      <i class="fas fa-folder-open fa-fw" aria-hidden="true"></i>
      <p>计算机/数据库</p>
    </a>
  </div>


            
          
            
              <div class="new-meta-item date">
  <a class='notlink'>
    <i class="fas fa-calendar-alt fa-fw" aria-hidden="true"></i>
    <p>发布于：2021年1月23日 14:17</p>
  </a>
</div>

            
          
            
              

            
          
        </div>
        
          <hr>
        
      </div>
    
  </section>


      <section class="article typo">
        <div class="article-entry" itemprop="articleBody">
          
          
          <h2 id="数据库系统的基本概念">数据库系统的基本概念</h2>
<h3 id="从数据到数据库">从数据到数据库</h3>
<h4 id="数据">数据</h4>
<h5 id="数据的概念">数据的概念</h5>
<p><strong>数据：</strong>数据的内容是信息；数据的表现形式是符号记录</p>
<h5 id="数据的分类">数据的分类</h5>
<ul>
<li><p>按数据加工程度分：分为三次数据、二次数据、一次数据、零次数据（加工程度由高到低）</p></li>
<li><p>按结构化程度分：</p></li>
</ul>
<table>
<thead>
<tr class="header">
<th>类型</th>
<th>含义</th>
<th>本质</th>
<th>举例</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td>结构化数据</td>
<td>直接可用关系型数据库存储和管理的数据</td>
<td>先有结构，后有数据</td>
<td>关系数据库中的数据</td>
</tr>
<tr class="even">
<td>半结构化数据</td>
<td>经过一定的加工处理以后可以用关系数据库存储和管理的数据</td>
<td>先有数据，后有结构</td>
<td>HTML、XML</td>
</tr>
<tr class="odd">
<td>非结构化数据</td>
<td>无法用关系型数据库存储和管理的数据</td>
<td>没有（或难以发现）统一结构的数据</td>
<td>语音、图像文件</td>
</tr>
</tbody>
</table>
<a id="more"></a>
<h4 id="数据库">数据库</h4>
<p><strong>数据库（Database, DB）：</strong>按一定结构存管数据的库</p>
<p><strong>数据库的特点：</strong>（1）集成性：特定的数据和联系集中在一起按一定结构存储；（2）共享性：数据库中的数据能被多个应用程序的用户使用</p>
<h4 id="数据库系统">数据库系统</h4>
<p><strong>数据库系统（Database System, DBS)</strong>又称数据库应用系统，是具有数据库技术支持的应用系统。如：网银系统、点餐系统、教务系统</p>
<p><strong>数据库管理系统（Database Management System，DBMS）</strong>是建立、操作、管理、控制数据库和数据的应用软件。如：SQL Server，MySQL，Oracle</p>
<p>注：DBS包含DB和DBMS及其它应用软件</p>
<h4 id="数据模型">数据模型</h4>
<p>数据从现实世界进入数据库，需要经历3个阶段</p>
<figure>
<img src="/junblog/2021/01/23/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%A6%82%E8%AE%BA%E5%A4%8D%E4%B9%A0/数据模型.png" alt="数据模型"><figcaption aria-hidden="true">数据模型</figcaption>
</figure>
<h4 id="概念模型e-r图的设计">概念模型：E-R图的设计</h4>
<p><strong>E-R图设计的基本方法：</strong> （1）确定实体 （2）确定联系类型（1:1, 1:n, n:m） （3）把实体类型和联系类型组成E-R图 （4）确定实体类型和联系类型的属性 （5）确定实体类型的主键</p>
<p>E-R图示例：餐馆点餐系统</p>
<figure>
<img src="/junblog/2021/01/23/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%A6%82%E8%AE%BA%E5%A4%8D%E4%B9%A0/ER图.png" alt="ER图"><figcaption aria-hidden="true">ER图</figcaption>
</figure>
<h4 id="数据库系统的模式结构">数据库系统的模式结构</h4>
<p>数据库系统通过3个层次的抽象，构成了三级模式结构</p>
<p>（1）外模式or子模式：对应视图 （2）逻辑模式or概念模式：对应概念数据库 （3）内模式or存储模式：对应物理数据库</p>
<p>三级模式结构的优点： （1）是数据库系统中最本质的系统结构 （2）数据共享 （3）简化用户接口 （4）数据安全</p>
<h3 id="t-sql常用操作语言的分类">T-SQL常用操作语言的分类</h3>
<ol type="1">
<li>数据定义语言（Data Definition Language，DDL） 对数据库、视图、表、索引的创建、修改、删除，主要是<code>CREATE</code>, <code>ALTER</code>, <code>DROP</code></li>
<li>数据操作语言（Data Manipulation Language，DML） 插入数据<code>INSERT</code>，更新数据<code>UPDATE</code>，删除数据<code>DELETE</code>，数据查询<code>SELECT</code></li>
<li>数据控制语言（Data Control Language，DCL） 对数据库进行安全管理和权限控制，如赋予权限GRANT，禁止赋予的权限DENY，收回权限REVOKE</li>
<li>事务管理语言（Transact Management Language，TML） 用于事务管理，提交事务COMMIT，撤销事务ROLLBACK</li>
<li>其它附加的语言：包括可编程性（函数、存储过程、触发器等）</li>
</ol>
<h2 id="数据定义语言">数据定义语言</h2>
<h3 id="数据库的创建修改删除">数据库的创建、修改、删除</h3>
<h4 id="数据库的创建">数据库的创建</h4>
<p>主数据文件<code>.mdf</code>（仅一个），次数据文件<code>.ndf</code>（可有多个），日志文件<code>.ldf</code>（可以有多个，不属于任何文件组）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/****** 建立数据库 ******/</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">DATABASE</span> restaurant</span><br><span class="line"><span class="keyword">ON</span> </span><br><span class="line">PRIMARY   <span class="comment">--指定文件组，默认为PRIMARY</span></span><br><span class="line">(</span><br><span class="line">    <span class="keyword">NAME</span> = restaurant_data,  <span class="comment">--指定数据文件逻辑名，不可省</span></span><br><span class="line">    FILENAME = <span class="string">'C:\sql\restaurant_data.mdf'</span>,   <span class="comment">--指定物理文件名</span></span><br><span class="line">    <span class="keyword">SIZE</span> = <span class="number">5</span> MB,   <span class="comment">--初始大小</span></span><br><span class="line">    <span class="keyword">MAXSIZE</span> = <span class="number">500</span> MB,   <span class="comment">--最大大小</span></span><br><span class="line">    FILEGROWTH = <span class="number">10</span> %   <span class="comment">--增长方式</span></span><br><span class="line">),</span><br><span class="line">FILEGROUP group01   <span class="comment">--指定文件组 group01</span></span><br><span class="line">(</span><br><span class="line">    <span class="keyword">NAME</span> = restaurant_data_group_01_01,  <span class="comment">--指定数据文件逻辑名，不可省</span></span><br><span class="line">    FILENAME = <span class="string">'C:\sql\restaurant_data_group01_01.ndf'</span>,   <span class="comment">--指定物理文件名</span></span><br><span class="line">    <span class="keyword">SIZE</span> = <span class="number">5</span> MB,   <span class="comment">--初始大小</span></span><br><span class="line">    <span class="keyword">MAXSIZE</span> = <span class="number">500</span> MB,   <span class="comment">--最大大小</span></span><br><span class="line">    FILEGROWTH = <span class="number">10</span> %   <span class="comment">--增长方式</span></span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="keyword">LOG</span> <span class="keyword">ON</span>   <span class="comment">--指明日志文件，不可省</span></span><br><span class="line">(</span><br><span class="line">    <span class="keyword">NAME</span> = restaurant_log,  <span class="comment">--指定逻辑名，不可省</span></span><br><span class="line">    FILENAME = <span class="string">'C:\sql\restaurant_log.ldf'</span>,</span><br><span class="line">    <span class="keyword">SIZE</span> = <span class="number">5</span> MB,</span><br><span class="line">    <span class="keyword">MAXSIZE</span> = <span class="number">100</span> MB,</span><br><span class="line">    FILEGROWTH = <span class="number">2</span> MB</span><br><span class="line">)</span><br><span class="line"><span class="keyword">GO</span></span><br></pre></td></tr></table></figure>
<h4 id="数据库的修改">数据库的修改</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/****** 重命名数据库 ******/</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">DATABASE</span> restaurant</span><br><span class="line"><span class="keyword">MODIFY</span> <span class="keyword">NAME</span> = restaurant_new   <span class="comment">--将数据库restaurant重命名为restaurant_new</span></span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/****** 增加数据文件 ******/</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">DATABASE</span> restaurant_new</span><br><span class="line">	<span class="comment">--向主文件组增加数据文件</span></span><br><span class="line"><span class="keyword">ADD</span> <span class="keyword">FILE</span> </span><br><span class="line"><span class="keyword">ON</span> PRIMARY	</span><br><span class="line">(</span><br><span class="line">    <span class="keyword">NAME</span> = restaurant_data_02,  <span class="comment">--指定数据库逻辑名，不可省</span></span><br><span class="line">    FILENAME = <span class="string">'C:\sql\restaurant_data_02.ndf'</span>,   <span class="comment">--指定物理文件名</span></span><br><span class="line">    <span class="keyword">SIZE</span> = <span class="number">5</span> MB,   <span class="comment">--初始大小</span></span><br><span class="line">    <span class="keyword">MAXSIZE</span> = <span class="number">500</span> MB,   <span class="comment">--最大大小</span></span><br><span class="line">    FILEGROWTH = <span class="number">10</span> %   <span class="comment">--增长方式</span></span><br><span class="line">)</span><br><span class="line"></span><br><span class="line">	<span class="comment">--向某一文件组增加数据文件</span></span><br><span class="line"><span class="keyword">ADD</span> <span class="keyword">FILE</span></span><br><span class="line">(</span><br><span class="line">    <span class="keyword">NAME</span> = restaurant_data_group_01_02,  <span class="comment">--指定数据文件逻辑名，不可省</span></span><br><span class="line">    FILENAME = <span class="string">'C:\sql\restaurant_data_group01_02.ndf'</span>,</span><br><span class="line">    <span class="keyword">SIZE</span> = <span class="number">5</span> MB,</span><br><span class="line">    <span class="keyword">MAXSIZE</span> = <span class="number">500</span> MB,</span><br><span class="line">    FILEGROWTH = <span class="number">10</span> % </span><br><span class="line">)</span><br><span class="line"><span class="keyword">TO</span> FILEGROUP group01</span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/****** 修改数据文件 ******/</span></span><br><span class="line">使用<span class="keyword">MODIFY</span> <span class="keyword">FILE</span> filename</span><br><span class="line"></span><br><span class="line"><span class="comment">/****** 增加、修改日志文件 ******/</span></span><br><span class="line">类似，使用<span class="keyword">ADD</span> <span class="keyword">LOG</span> <span class="keyword">FILE</span>, <span class="keyword">MODIFY</span> <span class="keyword">FILE</span>(注意没有<span class="keyword">MODIFY</span> <span class="keyword">LOG</span> <span class="keyword">FILE</span>)</span><br></pre></td></tr></table></figure>
<h4 id="数据库的删除">数据库的删除</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">DATABASE</span> restaurant_new</span><br><span class="line"><span class="keyword">GO</span></span><br></pre></td></tr></table></figure>
<h3 id="表的创建修改删除">表的创建、修改、删除</h3>
<h4 id="表的创建">表的创建</h4>
<p>以餐馆点餐系统为例</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> Customer</span><br><span class="line">(</span><br><span class="line">    customer_no <span class="built_in">INT</span> <span class="keyword">IDENTITY</span>(<span class="number">1000000</span>,<span class="number">1</span>) PRIMARY <span class="keyword">KEY</span>,</span><br><span class="line">	nickname <span class="built_in">CHAR</span>(<span class="number">20</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">UNIQUE</span>,</span><br><span class="line">	phone <span class="built_in">CHAR</span>(<span class="number">16</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">UNIQUE</span>,</span><br><span class="line">	pass <span class="built_in">VARCHAR</span>(<span class="number">255</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">    is_vip <span class="built_in">CHAR</span>(<span class="number">2</span>) <span class="keyword">CHECK</span>(is_vip=<span class="string">'是'</span> <span class="keyword">OR</span> is_vip=<span class="string">'否'</span>) <span class="keyword">DEFAULT</span> <span class="string">'否'</span></span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> Menu</span><br><span class="line">(</span><br><span class="line">    dish_no <span class="built_in">CHAR</span>(<span class="number">8</span>) PRIMARY <span class="keyword">KEY</span>,</span><br><span class="line">    dish_name <span class="built_in">CHAR</span>(<span class="number">20</span>) <span class="keyword">UNIQUE</span> <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">	discount <span class="built_in">NUMERIC</span>(<span class="number">3</span>,<span class="number">2</span>) <span class="keyword">DEFAULT</span> <span class="number">1</span>,</span><br><span class="line">    price <span class="built_in">NUMERIC</span>(<span class="number">6</span>,<span class="number">2</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">    is_spicy <span class="built_in">CHAR</span>(<span class="number">8</span>) <span class="keyword">CHECK</span>(is_spicy=<span class="string">'不辣'</span> <span class="keyword">OR</span> is_spicy=<span class="string">'微辣'</span> <span class="keyword">OR</span> is_spicy=<span class="string">'辣'</span> <span class="keyword">OR</span> is_spicy=<span class="string">'很辣'</span>)</span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> Orders</span><br><span class="line">(</span><br><span class="line">    order_no <span class="built_in">INT</span> <span class="keyword">IDENTITY</span>(<span class="number">1000000</span>,<span class="number">1</span>) PRIMARY <span class="keyword">KEY</span>,</span><br><span class="line">    customer_no <span class="built_in">INT</span> <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> <span class="keyword">REFERENCES</span> Customer(customer_no) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">    employee_no <span class="built_in">CHAR</span>(<span class="number">8</span>) <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> <span class="keyword">REFERENCES</span> Employee(employee_no),</span><br><span class="line">    order_time DATETIME,</span><br><span class="line">    people_num <span class="built_in">INT</span>,</span><br><span class="line">    discount <span class="built_in">NUMERIC</span>(<span class="number">3</span>,<span class="number">2</span>) <span class="keyword">DEFAULT</span> <span class="number">1</span>,</span><br><span class="line">	total_price <span class="built_in">NUMERIC</span>(<span class="number">6</span>,<span class="number">2</span>),</span><br><span class="line">    order_status <span class="built_in">CHAR</span>(<span class="number">6</span>) <span class="keyword">CHECK</span>(order_status <span class="keyword">IN</span> (<span class="string">'未提交'</span>,<span class="string">'待付款'</span>,<span class="string">'已接单'</span>,<span class="string">'已完成'</span>,<span class="string">'已评价'</span>)),</span><br><span class="line">    <span class="comment">/**************************************** </span></span><br><span class="line"><span class="comment">    如果想给约束起个名字，可以使用表级约束，然后使用CONSTRAINT起名</span></span><br><span class="line"><span class="comment">    CONSTRAINT CK_order_status    CHECK(order_status IN ('未提交','待付款','已接单','已完成','已评价'))</span></span><br><span class="line"><span class="comment">    *****************************************/</span></span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> Orders_content</span><br><span class="line">(</span><br><span class="line">	order_no <span class="built_in">INT</span> <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> <span class="keyword">REFERENCES</span> Orders(order_no),</span><br><span class="line">	dish_no <span class="built_in">CHAR</span>(<span class="number">8</span>) <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> <span class="keyword">REFERENCES</span> Menu(dish_no),</span><br><span class="line">	PRIMARY <span class="keyword">KEY</span> (order_no,dish_no)</span><br><span class="line">    <span class="comment">/*****************************************</span></span><br><span class="line"><span class="comment">    使用表级外键约束</span></span><br><span class="line"><span class="comment">    FOREIGN KEY (order_no) REFERENCES Orders(order_no),</span></span><br><span class="line"><span class="comment">    FOREIGN KEY (dish_no) REFERENCES Menu(dish_no)</span></span><br><span class="line"><span class="comment">    ******************************************/</span></span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<h4 id="补充使用查询结果来创建新表select-into">*补充：使用查询结果来创建新表：SELECT INTO</h4>
<p><code>SELECT INTO</code> 字句根据 SELECT命令选择的列和WHERE字句选择的行创建新表。它和CREATE TABLE命令的最大区别就是：不必经历通常的数据定义过程，而是基于现有字段和数据直接生成新表。</p>
<p>注意：SELECT INTO创建的表是一个真实的、独立的、永久的表，这与视图所对应的虚表不同。</p>
<h4 id="表的修改">表的修改</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> Orders</span><br><span class="line">	<span class="comment">/****** 修改某列定义 ******/</span></span><br><span class="line">	<span class="keyword">ALTER</span> <span class="keyword">COLUMN</span> people_num <span class="built_in">INT</span> <span class="keyword">NOT</span> <span class="literal">NULL</span></span><br><span class="line">	</span><br><span class="line">	<span class="comment">/****** 添加一列 ******/</span></span><br><span class="line">	<span class="keyword">ADD</span> remarks <span class="built_in">VARCHAR</span>(<span class="number">200</span>)</span><br><span class="line">	</span><br><span class="line">	<span class="comment">/****** 删除某列/某约束 ******/</span></span><br><span class="line">	<span class="keyword">DROP</span> <span class="keyword">COLUMN</span> discount</span><br><span class="line">	<span class="keyword">DROP</span> <span class="keyword">CONSTRAINT</span> CK_order_status</span><br></pre></td></tr></table></figure>
<p>不是所有列都可以被修改，也不可以对某列进行任意的修改，否则表中的数据可能发生错误。为了避免此种情况发生，SQL Server中规定：</p>
<p>具有以下特性的列不能被修改：</p>
<ul>
<li>数据类型为timestamp的列</li>
<li>计算列</li>
<li>全局标识符列</li>
<li>用于索引的列</li>
<li>用于由<code>CREATE STATISTICS</code>生成统计的列</li>
<li>用于主键或外键约束的列</li>
<li>关联有默认值的列</li>
</ul>
<p>改变列的数据类型时，要满足以下条件：</p>
<ul>
<li>原数据类型必须能够转换至新类型</li>
<li>新类型不能为timestamp类型</li>
<li>如果被修改列属性中有“标识规范”属性，则新数据类型必须是有效的“标识规范”数据类型</li>
</ul>
<p>总之，不要轻易修改表结构。</p>
<h4 id="表的删除">表的删除</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> Orders_content</span><br></pre></td></tr></table></figure>
<h3 id="视图的创建修改删除">视图的创建、修改、删除</h3>
<h4 id="何为视图">何为视图？</h4>
<p>视图的定义是：为了得到另一个关系而对基关系进行一次或多次操作所得到的动态结果。</p>
<p>通俗地说：视图就是一个在已有表或视图中进行子查询得到的虚表，在数据库中并不存在，需要时根据要求临时生成。</p>
<p>视图对应三级模式中的外模式。</p>
<p><strong>视图的种类：</strong></p>
<ol type="1">
<li>标准视图：用户定义的视图（内容为一个查询）</li>
<li>索引视图：具体化的视图，可以为其建立索引（关键步骤：使用 WITH SCHEMABINDING 选项创建视图，并为视图创建唯一的聚集索引。）</li>
<li>分区视图</li>
<li>系统视图</li>
</ol>
<p><strong>注意：</strong></p>
<ol type="1">
<li>不能将规则或DEFAULT定义与视图关联</li>
<li>不能将AFTER触发器与视图相关联，只有INSTEAD OF触发器可以与之相关联</li>
<li>定义视图的查询不能包含ORDER BY子句，除非在SELECT语句中有TOP子句</li>
<li>不能创建临时视图，也不能为临时表创建视图</li>
</ol>
<h4 id="创建视图">创建视图</h4>
<p>语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">VIEW</span> viewName [(newColumnName [,...] )]</span><br><span class="line">[<span class="keyword">WITH</span> ENCRYPTION | <span class="keyword">WITH</span> SECHEMABINDING]</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line"><span class="keyword">SELECT</span> ... <span class="keyword">FROM</span> ...</span><br><span class="line">[<span class="keyword">WITH</span> [<span class="keyword">CASCADED</span>|<span class="keyword">LOCAL</span>] <span class="keyword">CHECK</span> <span class="keyword">OPTION</span>] </span><br><span class="line"><span class="comment">--要求对该视图执行的所有数据修改语句都必须符合select语句中设置的条件。CASCADED CHECK OPTION指明级联检查是否符合视图的定义（比如B视图在A视图基础上创建，级联检查就是既检查是否符合B视图的定义，又检查是否符合A视图的定义。）LOCAL CHECK OPTION就是只检查是否符合当前视图的定义。</span></span><br></pre></td></tr></table></figure>
<p>例子：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">VIEW</span> every_order (订单号, 菜品号, 菜品反馈)</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line"><span class="keyword">SELECT</span> order_no, dish_no, dish_feedback</span><br><span class="line"><span class="keyword">FROM</span> Orders_content</span><br><span class="line"><span class="keyword">GO</span></span><br></pre></td></tr></table></figure>
<h4 id="修改视图">修改视图</h4>
<p>语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">VIEW</span> viewName [(newColumnName [,...] )]</span><br><span class="line">[<span class="keyword">WITH</span> ENCRYPTION | <span class="keyword">WITH</span> SECHEMABINDING]</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line"><span class="keyword">SELECT</span> ... <span class="keyword">FROM</span> ...</span><br><span class="line">[<span class="keyword">WITH</span> [<span class="keyword">CASCADED</span>|<span class="keyword">LOCAL</span>] <span class="keyword">CHECK</span> <span class="keyword">OPTION</span>]</span><br></pre></td></tr></table></figure>
<p>注：其实就是把CREATE改为ALTER。</p>
<h4 id="删除视图">删除视图</h4>
<p>语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">VIEW</span> viewName</span><br></pre></td></tr></table></figure>
<p>实际操作的时候可以先检查该名称的视图是否存在。检查的方法：利用系统表<code>INFORMATION_SCHEMA.VIEWS</code>中<code>TABLE_NAME</code>列（该列存放视图名字）</p>
<p>例子：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">USE</span> restaurant</span><br><span class="line"><span class="keyword">IF</span> <span class="keyword">EXISTS</span>(<span class="keyword">SELECT</span> TABLE_NAME <span class="keyword">FROM</span> INFORMATION_SCHEMA.VIEWS <span class="keyword">WHERE</span> TABLE_NAME = <span class="string">'every_order'</span>)</span><br><span class="line">	<span class="keyword">DROP</span> <span class="keyword">VIEW</span> every_order</span><br><span class="line"><span class="keyword">GO</span></span><br></pre></td></tr></table></figure>
<h4 id="利用视图更新数据">利用视图更新数据</h4>
<p>视图虽然是虚表，但由于它是最终都是建立在实表的基础上的，因此也承载了更新数据的功能。对视图中数据的更新最终要转化为对基本表的更新。</p>
<p>为了防止用户对不属于视图范围内的数据进行操作，定义视图时最好加上<code>WITH CHECK OPTION</code>选项</p>
<p><strong>什么样的视图是可更新视图？</strong></p>
<ol type="1">
<li>创建视图的SELECT语句中没有聚合函数，也没有<code>TOP</code>, <code>GROUP BY</code>, <code>HAVING</code>和<code>DISTINCT</code>关键字</li>
<li>创建视图的<code>SELECT</code>语句中的各列必须是来自基表/视图的列，不能是表达式</li>
<li>若视图包括多个基础表，且要更改的列属于不同基础表或为基础表的公共列，则不能通过视图更改基础表的数据</li>
</ol>
<p>满足更新视图的要求的情况下，在视图上插入<code>INSERT</code>，更新<code>UPDATE</code>，删除<code>REMOVE</code>数据的语法和对表的操作完全相同。</p>
<p>关键在于思考我们在视图上修改表数据的操作是否是被允许的。</p>
<h3 id="索引的创建修改删除">索引的创建、修改、删除</h3>
<h4 id="创建索引">创建索引</h4>
<p>创建索引的语法</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> [<span class="keyword">UNIQUE</span>] [CLUSTERED|NONCLUSTERED] <span class="keyword">INDEX</span></span><br><span class="line"><span class="keyword">ON</span> tableName (columnName [<span class="keyword">ASC</span>|<span class="keyword">DESC</span>] [,...])</span><br></pre></td></tr></table></figure>
<p>例子：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> CLUSTERED <span class="keyword">INDEX</span> index_order_date</span><br><span class="line"><span class="keyword">ON</span> Orders(order_time <span class="keyword">DESC</span>)</span><br><span class="line"><span class="keyword">GO</span></span><br></pre></td></tr></table></figure>
<p>聚集索引决定数据存放顺序，在一张表中只能有一个</p>
<p>非聚集索引在表中可以有多个</p>
<h4 id="删除索引">删除索引</h4>
<p>语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">INDEX</span> indexName</span><br></pre></td></tr></table></figure>
<h4 id="索引的分类">索引的分类</h4>
<ul>
<li>按存储结构：分为聚集索引、非聚集索引</li>
<li>按数据的唯一性：分为唯一索引和非唯一索引</li>
</ul>
<h3 id="使用规则默认值和约束保证数据完整性">使用规则、默认值和约束保证数据完整性</h3>
<h4 id="规则">规则</h4>
<h5 id="创建规则">创建规则</h5>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> RULE rule_discount</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line">@discount &lt;= <span class="number">1.00</span> <span class="keyword">AND</span> @discount &gt; <span class="number">0</span></span><br></pre></td></tr></table></figure>
<h5 id="绑定规则">绑定规则</h5>
<p>将规则绑定至某张表的某一列，一列至多绑定一个规则。</p>
<p>规则与CHECK约束作用类似，但形式不同：</p>
<ul>
<li>规则是依附于表的，而CHECK约束不依附于表（它可以对列应用，对表应用，也可以作为一个CONSTRAINT存在）</li>
<li>CHECK约束优先级高于规则</li>
<li>一个列中可以有多个CHECK约束，但是只能有一个规则。可以同时有CHECK约束和规则。</li>
</ul>
<p>绑定规则需要使用系统存储过程（SQL Server）</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">EXEC sp_bindrule rule_discount, &#39;Orders.discount&#39;</span><br><span class="line">-- 参数分别为 规则名, 表名.列名</span><br></pre></td></tr></table></figure>
<h5 id="解绑规则">解绑规则</h5>
<p>使用系统存储过程（SQL Server）解绑规则</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">EXEC sp_unbindrule &#39;Orders.discount&#39;</span><br><span class="line">-- 参数名为列名</span><br></pre></td></tr></table></figure>
<p>注：为何参数名只有列名？因为一个列只能绑定一个规则，所以给出列名足以使DBMS明白需要解绑的是哪个规则。</p>
<h5 id="删除规则">删除规则</h5>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> RULE rule_discount</span><br><span class="line"><span class="comment">-- 直接后接规则名即可，可以跟多个</span></span><br></pre></td></tr></table></figure>
<h4 id="默认值">默认值</h4>
<h5 id="创建默认值">创建默认值</h5>
<p>语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">DEFAULT</span> defaultName </span><br><span class="line"><span class="keyword">AS</span> const_expression</span><br></pre></td></tr></table></figure>
<p>例子：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">DEFAULT</span> default_discount</span><br><span class="line"><span class="keyword">AS</span> <span class="number">1.00</span></span><br></pre></td></tr></table></figure>
<h5 id="绑定默认值">绑定默认值</h5>
<p>只能使用系统存储过程，与绑定规则类似</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">EXEC sp_bindefault default_discount, &#39;Orders.discount&#39;</span><br></pre></td></tr></table></figure>
<p><em>:exclamation: 注意是bindefault而不是binddefault，省略了一个d !</em></p>
<h5 id="解绑默认值">解绑默认值</h5>
<p>（SQL Server）</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">sp_unbindefault &#39;Orders.discount&#39;</span><br></pre></td></tr></table></figure>
<h5 id="删除默认值">删除默认值</h5>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">DEFAULT</span> default_discount</span><br><span class="line"><span class="comment">-- 参数为 默认值名称</span></span><br></pre></td></tr></table></figure>
<p>注意：删除默认值的时候必须先解绑，否则出错！</p>
<h4 id="约束">约束</h4>
<p>前面实际上已经在创建表的时候使用过表级约束和列级约束，那时我们虽然没有使用CONSTRAINT关键字，但系统已经将其作为一个CONSTRAINT看待，并给它赋予了一个名称（不过这个名称通常异常复杂）。如果我们显式地使用CONSTRAINT关键字，那么可以给约束起名字，方便重复使用。</p>
<p>SQL Server中共有6中约束：</p>
<ul class="task-list">
<li><input type="checkbox" disabled>
主键约束（PRIMARY KEY）</li>
<li><input type="checkbox" disabled>
外键约束（FOREIGN KEY）</li>
<li><input type="checkbox" disabled>
唯一性约束（UNIQUE）</li>
<li><input type="checkbox" disabled>
检查约束（CHECK）</li>
<li><input type="checkbox" disabled>
默认约束（DEFAULT）</li>
<li><input type="checkbox" disabled>
非空值约束（NOT NULL）</li>
</ul>
<p>你都还有印象吗？</p>
<p>尝试：试将下例中列级约束全部改为显式指定CONSTRAINT的表级约束</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> Menu</span><br><span class="line">(</span><br><span class="line">    dish_no <span class="built_in">CHAR</span>(<span class="number">8</span>) PRIMARY <span class="keyword">KEY</span>,</span><br><span class="line">    dish_name <span class="built_in">CHAR</span>(<span class="number">20</span>) <span class="keyword">UNIQUE</span> <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">	discount <span class="built_in">NUMERIC</span>(<span class="number">3</span>,<span class="number">2</span>) <span class="keyword">DEFAULT</span> <span class="number">1</span>,</span><br><span class="line">    price <span class="built_in">NUMERIC</span>(<span class="number">6</span>,<span class="number">2</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">    is_spicy <span class="built_in">CHAR</span>(<span class="number">8</span>) <span class="keyword">CHECK</span>(is_spicy=<span class="string">'不辣'</span> <span class="keyword">OR</span> is_spicy=<span class="string">'微辣'</span> <span class="keyword">OR</span> is_spicy=<span class="string">'辣'</span> <span class="keyword">OR</span> is_spicy=<span class="string">'很辣'</span>)</span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> Orders</span><br><span class="line">(</span><br><span class="line">    order_no <span class="built_in">INT</span> <span class="keyword">IDENTITY</span>(<span class="number">1000000</span>,<span class="number">1</span>) PRIMARY <span class="keyword">KEY</span>,</span><br><span class="line">    customer_no <span class="built_in">INT</span> <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> <span class="keyword">REFERENCES</span> Customer(customer_no) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">    employee_no <span class="built_in">CHAR</span>(<span class="number">8</span>) <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> <span class="keyword">REFERENCES</span> Employee(employee_no),</span><br><span class="line">    order_time DATETIME,</span><br><span class="line">    people_num <span class="built_in">INT</span>,</span><br><span class="line">    service_feedback <span class="built_in">NUMERIC</span>(<span class="number">2</span>,<span class="number">1</span>),</span><br><span class="line">    discount <span class="built_in">NUMERIC</span>(<span class="number">3</span>,<span class="number">2</span>) <span class="keyword">DEFAULT</span> <span class="number">1</span>,</span><br><span class="line">	total_price <span class="built_in">NUMERIC</span>(<span class="number">6</span>,<span class="number">2</span>)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 修改后</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> Menu</span><br><span class="line">(</span><br><span class="line">    dish_no <span class="built_in">CHAR</span>(<span class="number">8</span>),</span><br><span class="line">    dish_name <span class="built_in">CHAR</span>(<span class="number">20</span>),</span><br><span class="line">	discount <span class="built_in">NUMERIC</span>(<span class="number">3</span>,<span class="number">2</span>),</span><br><span class="line">    price <span class="built_in">NUMERIC</span>(<span class="number">6</span>,<span class="number">2</span>),</span><br><span class="line">    is_spicy <span class="built_in">CHAR</span>(<span class="number">8</span>)，</span><br><span class="line">    </span><br><span class="line">    <span class="keyword">CONSTRAINT</span> pk_dish_no PRIMARY <span class="keyword">KEY</span> (dish_no),</span><br><span class="line">    <span class="keyword">CONSTRAINT</span> uni_dish_name <span class="keyword">UNIQUE</span> (dish_name),</span><br><span class="line">    <span class="keyword">CONSTRAINT</span> default_discount <span class="keyword">DEFAULT</span> <span class="number">1</span> <span class="keyword">FOR</span> (discount),</span><br><span class="line">    <span class="keyword">CONSTRAINT</span> notnull_price <span class="keyword">NOT</span> <span class="literal">NULL</span> (dish_name, price),</span><br><span class="line">    <span class="keyword">CONSTRAINT</span> ck_is_spicy <span class="keyword">CHECK</span> (is_spicy <span class="keyword">IN</span> (<span class="string">'不辣'</span>, ‘微辣<span class="string">', '</span>辣<span class="string">', '</span>很辣<span class="string">') )</span></span><br><span class="line"><span class="string">)</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">CREATE TABLE Orders</span></span><br><span class="line"><span class="string">(</span></span><br><span class="line"><span class="string">    order_no INT IDENTITY(1000000,1) PRIMARY KEY,</span></span><br><span class="line"><span class="string">    customer_no INT FOREIGN KEY REFERENCES Customer(customer_no) NOT NULL,</span></span><br><span class="line"><span class="string">    employee_no CHAR(8) FOREIGN KEY REFERENCES Employee(employee_no),</span></span><br><span class="line"><span class="string">    order_time DATETIME,</span></span><br><span class="line"><span class="string">    people_num INT,</span></span><br><span class="line"><span class="string">    service_feedback NUMERIC(2,1),</span></span><br><span class="line"><span class="string">	total_price NUMERIC(6,2),</span></span><br><span class="line"><span class="string">    </span></span><br><span class="line"><span class="string">    CONSTRAINT pk_order_no PRIMARY KEY (order_no),</span></span><br><span class="line"><span class="string">    CONSTRAINT notnull_customer_no NOT NULL (customer_no),</span></span><br><span class="line"><span class="string">	CONSTRAINT fk_customer_no FOREIGN KEY (customer_no) REFERENCES Customer(customer_no),</span></span><br><span class="line"><span class="string">    CONSTRAINT fk_employee_no FOREIGN KEY (employee_no) REFERENCES Employee(employee_no),</span></span><br><span class="line"><span class="string">)</span></span><br></pre></td></tr></table></figure>
<h2 id="数据操作语言">数据操作语言</h2>
<h3 id="数据的添加更新和删除">数据的添加、更新和删除</h3>
<h4 id="数据的添加">数据的添加</h4>
<p>假设Menu表的逻辑结构是Menu(<u>dish_no</u>, dish_name, discount, price, is_spicy)</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/******** 往所有列上插入数据 ********/</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Menu</span><br><span class="line"><span class="keyword">VALUES</span></span><br><span class="line">(<span class="string">'001'</span>, <span class="string">'小炒黄牛肉'</span>, <span class="number">1.00</span>, <span class="number">66.00</span>, <span class="string">'辣'</span>),</span><br><span class="line">(<span class="string">'002'</span>, <span class="string">'柠檬脆皮鸭'</span>, <span class="number">0.85</span>, <span class="number">58.00</span>, <span class="string">'不辣'</span>)</span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/******** 往指定列上插入数据 ********/</span></span><br><span class="line"><span class="comment">-- 用括号给出指定列的列名</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Menu (dish_no, dish_name, price, is_spicy)</span><br><span class="line"><span class="keyword">VALUES</span></span><br><span class="line">(<span class="string">'001'</span>, <span class="string">'小炒黄牛肉'</span>, <span class="number">66.00</span>, <span class="string">'辣'</span>),</span><br><span class="line">(<span class="string">'002'</span>, <span class="string">'柠檬脆皮鸭'</span>, <span class="number">58.00</span>, <span class="string">'不辣'</span>)</span><br><span class="line"><span class="keyword">GO</span></span><br></pre></td></tr></table></figure>
<p>INSERT后面也可以直接跟一张表（要求表的字段名和INSERT INTO的那张表匹配），这样可以将表中数据直接全部添加进去。如：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Menu</span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> Menu2</span><br></pre></td></tr></table></figure>
<h4 id="数据的更新">数据的更新</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">UPDATE</span> Menu</span><br><span class="line"><span class="keyword">SET</span> price = <span class="number">45.00</span></span><br><span class="line"><span class="keyword">WHERE</span> dish_no = <span class="string">'001'</span></span><br></pre></td></tr></table></figure>
<h4 id="数据的删除">数据的删除</h4>
<p>DELETE语句删除的内容会记入日志，而TRUNCATE语句不会把每一行的删除操作记入日志</p>
<h5 id="使用delete语句删除">使用DELETE语句删除</h5>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/******** 删除表中所有行 ********/</span></span><br><span class="line"><span class="keyword">DELETE</span> Orders </span><br><span class="line"><span class="comment">-- 等价于DELETE FROM Orders，因为FROM可以省略</span></span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/******** 删除表中某些行 ********/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 用WHERE字句筛选</span></span><br><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> Orders</span><br><span class="line"><span class="keyword">WHERE</span> Order_id = <span class="string">'1000001'</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 用TOP筛选</span></span><br><span class="line"><span class="keyword">DELETE</span> TOP <span class="number">10</span> <span class="keyword">FROM</span> Orders</span><br></pre></td></tr></table></figure>
<h5 id="使用truncate语句删除">使用TRUNCATE语句删除</h5>
<p>TRUNCATE语句一次删去表中所有数据</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">TRUNCATE</span> <span class="keyword">TABLE</span> Orders</span><br></pre></td></tr></table></figure>
<h3 id="数据查询">数据查询</h3>
<p>简单的数据查询此处略，复杂的放在可编程性之后</p>
<h2 id="可编程性">可编程性</h2>
<h3 id="变量及常量">变量及常量</h3>
<p>此处注意<code>@</code>开头为用户定义的变量，<code>@@</code>开头为全局变量</p>
<h3 id="程序控制流语句">程序控制流语句</h3>
<p>灵活使用CASE函数</p>
<blockquote>
<p>例子：</p>
<p>Menu表中is_spicy 有4种取值('不辣', '微辣', '辣', '很辣')，现希望对其进行如下查询</p>
<p>（1）输出形如</p>
<table>
<thead>
<tr class="header">
<th style="text-align: center;">口味</th>
<th style="text-align: center;">数量</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td style="text-align: center;">不辣</td>
<td style="text-align: center;">2</td>
</tr>
<tr class="even">
<td style="text-align: center;">微辣</td>
<td style="text-align: center;">3</td>
</tr>
<tr class="odd">
<td style="text-align: center;">辣</td>
<td style="text-align: center;">5</td>
</tr>
<tr class="even">
<td style="text-align: center;">很辣</td>
<td style="text-align: center;">4</td>
</tr>
</tbody>
</table>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> is_spicy <span class="keyword">AS</span> 口味, <span class="keyword">COUNT</span>(*) <span class="keyword">AS</span> 数量</span><br><span class="line"><span class="keyword">FROM</span> Menu</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> is_spicy</span><br></pre></td></tr></table></figure>
<p>（2）输出形如</p>
<table>
<thead>
<tr class="header">
<th style="text-align: center;">微辣</th>
<th style="text-align: center;">不辣</th>
<th style="text-align: center;">辣</th>
<th style="text-align: center;">很辣</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td style="text-align: center;">3</td>
<td style="text-align: center;">2</td>
<td style="text-align: center;">5</td>
<td style="text-align: center;">4</td>
</tr>
</tbody>
</table>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> </span><br><span class="line"><span class="keyword">COUNT</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span> is_spicy = <span class="string">'微辣'</span> <span class="keyword">THEN</span> <span class="number">1</span> <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>) <span class="keyword">AS</span> <span class="string">'微辣'</span></span><br><span class="line"><span class="keyword">COUNT</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span> is_spicy = <span class="string">'不辣'</span> <span class="keyword">THEN</span> <span class="number">1</span> <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>) <span class="keyword">AS</span> <span class="string">'不辣'</span></span><br><span class="line"><span class="keyword">COUNT</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span> is_spicy = <span class="string">'辣'</span> <span class="keyword">THEN</span> <span class="number">1</span> <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>) <span class="keyword">AS</span> <span class="string">'辣'</span></span><br><span class="line"><span class="keyword">COUNT</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span> is_spicy = <span class="string">'很辣'</span> <span class="keyword">THEN</span> <span class="number">1</span> <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>) <span class="keyword">AS</span> <span class="string">'很辣'</span></span><br><span class="line"><span class="keyword">FROM</span> Menu</span><br></pre></td></tr></table></figure>
</blockquote>
<h3 id="函数">函数</h3>
<h4 id="标量函数">标量函数</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">FUNCTION</span> function_name (@parameter_name parameter_type)</span><br><span class="line"><span class="keyword">RETURNS</span> return_type</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line">	function_body</span><br><span class="line">	<span class="keyword">RETURN</span> <span class="keyword">var</span></span><br></pre></td></tr></table></figure>
<h4 id="内嵌表值函数">内嵌表值函数</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">FUNCTION</span> function_name (@parameter_name parameter_type)</span><br><span class="line"><span class="keyword">RETURNS</span> <span class="keyword">TABLE</span> </span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line">	function_body</span><br><span class="line">	<span class="keyword">RETURN</span> (<span class="keyword">SELECT</span> column1, column2, ... )</span><br></pre></td></tr></table></figure>
<h4 id="多语句表值函数">多语句表值函数</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">FUNCTION</span> function_name (@parameter_name parameter_type)</span><br><span class="line"><span class="keyword">RETURNS</span> @return_tablename <span class="keyword">TABLE</span> (</span><br><span class="line">        <span class="keyword">Name</span> <span class="built_in">char</span>(<span class="number">8</span>),</span><br><span class="line">        sno <span class="built_in">char</span>(<span class="number">10</span>)</span><br><span class="line">)</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">        function_body</span><br><span class="line">        <span class="keyword">RETURN</span>   —返回的是@return_tablename那张表</span><br><span class="line"><span class="keyword">END</span></span><br></pre></td></tr></table></figure>
<p>注：<code>AS</code>后面的<code>BEGIN...END...</code>均可省略</p>
<h4 id="执行用户自定义函数">执行用户自定义函数</h4>
<p>使用时需要加上架构名（dbo） 如执行标量函数DatetoQuarter的语句为<code>SELECT dbo.DatetoQuarter(‘2020-4-5’)</code> 执行表值函数dish_ranking的语句为<code>SELECT * FROM dbo.dish_ranking()</code></p>
<h4 id="修改删除函数">修改、删除函数</h4>
<p>修改使用<code>ALTER TABLE</code></p>
<p><code>sp_help function_name</code>查看函数的信息</p>
<p><code>sp_helptext function_name</code>查看函数的定义</p>
<blockquote>
<p>例子：（待完善）字符串类型的聚合统计查询 将表中的人名（name列）进行聚合（按行访问使用游标） 不使用游标的做法：将每一次取得的name累加</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DECLARE</span> @<span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">200</span>)</span><br><span class="line"><span class="keyword">SET</span> @<span class="keyword">name</span> = ‘’</span><br><span class="line"><span class="keyword">SELECT</span> @<span class="keyword">name</span> = @<span class="keyword">name</span> + ‘, ’ <span class="keyword">name</span> <span class="keyword">FROM</span> student  — 但是第一个人名字之前有逗号，使用<span class="keyword">substring</span>库函数</span><br><span class="line"></span><br><span class="line"><span class="keyword">SET</span> @<span class="keyword">name</span> = <span class="keyword">substring</span>(@<span class="keyword">name</span>, <span class="number">2</span>, <span class="keyword">LEN</span>(@<span class="keyword">name</span>) )</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>实现字符串类型的聚合统计查询 抽象成自定义标量值函数</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">FUNCTION</span> mergenames (＠tdno <span class="built_in">int</span>)</span><br><span class="line"><span class="keyword">RETURNS</span> <span class="built_in">varchar</span> (<span class="number">512</span>)</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">    <span class="keyword">DECLARE</span> @allnames <span class="built_in">VARCHAR</span>(<span class="number">512</span>)</span><br><span class="line">    <span class="keyword">SET</span> @allnames=’’</span><br><span class="line">    <span class="keyword">IF</span> @tdno <span class="keyword">is</span> <span class="literal">null</span></span><br><span class="line">    <span class="keyword">SELECT</span> @allnames=@allnames +’, ’+<span class="keyword">RTRIM</span>(<span class="keyword">LTRIM</span>(sname) <span class="keyword">FROM</span> student </span><br><span class="line">    <span class="keyword">ELSE</span></span><br><span class="line">    <span class="keyword">SELECT</span> @allnames=@allnames+’, ’ + <span class="keyword">RTRIM</span>(<span class="keyword">LTRIM</span>(sname) ) <span class="keyword">FROM</span> student <span class="keyword">WHERE</span> dno= @tdno</span><br><span class="line">    <span class="keyword">RETURN</span> <span class="keyword">substring</span> (@allnames, <span class="number">2</span>, <span class="keyword">len</span>(@allnames) )</span><br><span class="line"><span class="keyword">END</span></span><br><span class="line"> </span><br><span class="line"><span class="keyword">SELECT</span> dno, dbo.mergenames(dno) </span><br><span class="line"><span class="keyword">FROM</span> student</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> dno</span><br></pre></td></tr></table></figure>
</blockquote>
<h3 id="游标">游标</h3>
<h4 id="游标的基本使用">游标的基本使用</h4>
<ol type="1">
<li>定义游标</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DECLARE</span> cur_customer <span class="keyword">CURSOR</span></span><br><span class="line"><span class="keyword">FOR</span> <span class="keyword">SELECT</span> <span class="keyword">id</span>, <span class="keyword">name</span>, addr, tel</span><br><span class="line"><span class="keyword">FROM</span> customer</span><br></pre></td></tr></table></figure>
<ol start="2" type="1">
<li>打开游标</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">OPEN cur_customer</span><br></pre></td></tr></table></figure>
<ol start="3" type="1">
<li>读取游标</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">FETCH NEXT FROM cur_customer</span><br><span class="line">FETCH PRIOR FROM cur_customer</span><br><span class="line">FETCH FIRST FROM cur_customer</span><br><span class="line">FETCH LAST FROM cur_customer</span><br></pre></td></tr></table></figure>
<ol start="4" type="1">
<li>关闭游标</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">CLOSE cur_customer</span><br></pre></td></tr></table></figure>
<ol start="5" type="1">
<li>释放游标</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DEALLOCATE</span> cur_customer</span><br></pre></td></tr></table></figure>
<p>注: DEALLOCATE 命令只删除游标对命名变量的引用</p>
<h4 id="进一步使用游标">进一步使用游标</h4>
<h5 id="使用游标函数">使用游标函数</h5>
<ol type="1">
<li><code>@@CURSOR_ROWS</code> 返回游标结果集里上次打开并读取的行数 打开游标后，该函数返回当前游标的总行数 static 的 cursor 才能使用 cursor rows</li>
<li><code>CURSOR_STATUS</code> 返回指定游标的状态</li>
<li><code>@@FETCH_STATUS</code> 返回被 FETCH 语句执行的最后游标的状态(不是当前被打开的游标的状态) 返回值: <code>0</code> FETCH 语句成功 <code>-1</code> FETCH 语句失败或者此行不在结果集里 <code>-2</code> 被提取的行不存在</li>
</ol>
<h5 id="用游标读取数据的一般方式">用游标读取数据的一般方式</h5>
<p>获取数据并存储到变量里面</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 定义变量用于保存数据</span></span><br><span class="line"><span class="keyword">DECLARE</span> @tmpId <span class="built_in">CHAR</span>(<span class="number">8</span>), @tmpName <span class="built_in">CHAR</span>(<span class="number">20</span>), @tmpAddr <span class="built_in">CHAR</span>(<span class="number">20</span>), @tmpTel <span class="built_in">CHAR</span>(<span class="number">12</span>)</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 定义游标</span></span><br><span class="line"><span class="keyword">DECLARE</span> cur_customer <span class="keyword">CURSOR</span></span><br><span class="line"><span class="keyword">FOR</span> <span class="keyword">SELECT</span> <span class="keyword">id</span>, <span class="keyword">name</span>, addr, tel</span><br><span class="line"><span class="keyword">FROM</span> customer</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 打开游标</span></span><br><span class="line"><span class="keyword">OPEN</span> cur_customer</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 读取游标</span></span><br><span class="line"><span class="keyword">FETCH</span> <span class="keyword">NEXT</span> <span class="keyword">FROM</span> cur_customer <span class="keyword">INTO</span> @tmpId, @tmpName, @tmpAddr, @tmpTel</span><br><span class="line"></span><br><span class="line"><span class="keyword">WHILE</span> @@fetch_status = <span class="number">0</span></span><br><span class="line">    <span class="keyword">BEGIN</span></span><br><span class="line">    PRINT(@tmpId + @tmpName + @tmpAddr + @tmpTel)</span><br><span class="line">    <span class="keyword">FETCH</span> <span class="keyword">NEXT</span> <span class="keyword">FROM</span> cur_customer <span class="keyword">INTO</span> @tmpId, @tmpName, @tmpAddr, @tmpTel  <span class="comment">-- 游标所在行的内容被按顺序读取至 4 个变量中</span></span><br><span class="line">    <span class="keyword">END</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 关闭游标</span></span><br><span class="line"><span class="keyword">CLOSE</span> cur_customer</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 释放游标</span></span><br><span class="line"><span class="keyword">DEALLOCATE</span> cur_customer</span><br></pre></td></tr></table></figure>
<h5 id="使用变量作为游标名游标变量">使用变量作为游标名(游标变量)</h5>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 定义游标变量</span></span><br><span class="line"><span class="keyword">DECLARE</span> @cur_var <span class="keyword">CURSOR</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 方式 1: 定义游标，再通过游标名赋值给游标变量</span></span><br><span class="line"><span class="keyword">DECLARE</span> cur_customer <span class="keyword">CURSOR</span></span><br><span class="line"><span class="keyword">FOR</span> <span class="keyword">SELECT</span> * <span class="keyword">FROM</span> customer</span><br><span class="line"></span><br><span class="line"><span class="keyword">SET</span> @cur_var = cur_customer</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 方式 2: (常用)创建游标，直接赋值给游标变量</span></span><br><span class="line"><span class="keyword">SET</span> @cur_var = <span class="keyword">CURSOR</span> <span class="keyword">FOR</span> <span class="keyword">SELECT</span> * <span class="keyword">FROM</span> customer</span><br></pre></td></tr></table></figure>
<h5 id="滚动游标的控制">滚动游标的控制</h5>
<p>用 SCROLL 声明游标以后，在 FETCH 语句中可以用: <code>NEXT</code>滚动到下一条 <code>PRIOR</code>滚动到前一条 <code>FIRST</code>滚动到第一条 <code>LAST</code>滚动到最后一条 <code>ABSOLUTE</code> 滚动到第几行 <code>RELATIVE</code> 正值为向下滚动的行数，负值为向上滚动的行数</p>
<h3 id="存储过程">存储过程</h3>
<p>存储过程是 SQL 语句或控制流语言的已命名集合。可以为常用功能创建存储过程以提高性能。SAP ASE 也提供系统过程来执行可更新系统表的管理任务。 存储过程可以:</p>
<ul>
<li>带参数</li>
<li>调用其它过程</li>
<li>把状态值返回给调用过程或批处理,以指明成功或失败,以及失败的原因</li>
<li>把参数的值返回给调用过程或批处理</li>
<li>在远程服务器上执行</li>
</ul>
<h4 id="创建存储过程">创建存储过程</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> procedureName @param_in [,...], @param_out <span class="keyword">OUTPUT</span> [,...]</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line">	procedure_body</span><br></pre></td></tr></table></figure>
<p>例子：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> add_to_cart @dish_no <span class="built_in">CHAR</span>(<span class="number">8</span>) ,@order_no <span class="built_in">INT</span></span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line">	<span class="keyword">INSERT</span> <span class="keyword">INTO</span> Orders_content</span><br><span class="line">	<span class="keyword">VALUES</span>(@order_no, @dish_no, <span class="literal">NULL</span>)</span><br><span class="line"><span class="keyword">GO</span></span><br></pre></td></tr></table></figure>
<h4 id="执行存储过程">执行存储过程</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">EXEC procedure_name value01 [,...], @var OUTPUT [,...]</span><br></pre></td></tr></table></figure>
<p>例子：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DECLARE</span> @order_no <span class="built_in">INT</span></span><br><span class="line">EXEC add_to_cart <span class="string">'001'</span>, @order_no <span class="keyword">OUTPUT</span></span><br></pre></td></tr></table></figure>
<h3 id="触发器">触发器</h3>
<p>触发器是一种特殊的存储过程</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TRIGGER</span> trigger_name</span><br><span class="line"><span class="keyword">ON</span> table_name</span><br><span class="line">[<span class="keyword">WITH</span> ENCRYPTION]</span><br><span class="line">[<span class="keyword">FOR</span> | <span class="keyword">AFTER</span> | INSTEAD <span class="keyword">OF</span>] [<span class="keyword">INSERT</span> | <span class="keyword">DELETE</span> | <span class="keyword">UPDATE</span>]</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line">    ......</span><br></pre></td></tr></table></figure>
<p>按发生的时间分: instead of，after 触发器 instead of 触发器: （事前预防） 生成两张临时表: inserted 和 deleted (如果合法，那么执行更新数据的操作) after 触发器：（事后检查）在操作完成后执行。 也生成两张临时表然后直接进行数据更改。可以撤销操作所造成的后果(触发器工作在事务模式下，如果检测出操作不合法，那么执行 rollback，如果合法，那么 after 触发器不执行任何操作) 在一张表中可以有多个</p>
<p>没有指定类型时是 after 类型的触发器</p>
<p>例子：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TRIGGER</span> add_total_price</span><br><span class="line"><span class="keyword">ON</span> Orders_content</span><br><span class="line"><span class="keyword">FOR</span> <span class="keyword">INSERT</span></span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line">	<span class="keyword">DECLARE</span> @price <span class="built_in">NUMERIC</span>(<span class="number">6</span>,<span class="number">2</span>)</span><br><span class="line">	<span class="keyword">SELECT</span> @price = <span class="keyword">SUM</span>(price*discount)</span><br><span class="line">	<span class="keyword">FROM</span> inserted <span class="keyword">INNER</span> <span class="keyword">JOIN</span> Menu <span class="keyword">ON</span> inserted.dish_no=Menu.dish_no</span><br><span class="line">	<span class="keyword">UPDATE</span> Orders</span><br><span class="line">	<span class="keyword">SET</span> total_price = (total_price + @price)*discount</span><br><span class="line">	<span class="keyword">WHERE</span> order_no <span class="keyword">IN</span> (<span class="keyword">SELECT</span> order_no <span class="keyword">FROM</span> inserted)</span><br><span class="line"><span class="keyword">GO</span></span><br></pre></td></tr></table></figure>
<p>DML 触发器:</p>
<ul>
<li>强制执行比 CHECK 约束更为复杂的约束</li>
<li>以多条语句的方式实行完整性检查</li>
<li>CHECK Constraint 是在 CREATE TABLE 的时候设置的，只能针对一张表中的数据，跨越两张表的完整性检查只能通过 DML 触发器检查</li>
<li>一张表上可以定义多个同类型的 DML触发器</li>
<li>主外键约束的完整性(跨越表)。使用 DML 跨越数据库的完整性约束(引用完整性 DRI 是不能跨越数据库的)</li>
<li>INSTEAD OF 触发器通过了，但触发器表的约束被违反，那么触发器运行后被回滚(执行顺序: INSTEAD OF 触发器-&gt;表约束-&gt;AFTER 触发器)</li>
</ul>
<h4 id="多个触发器的执行顺序">多个触发器的执行顺序</h4>
<p>如果有多个 AFTER 触发器，那么可以通过<code>sp_settriggerorder</code>可以执行第一个和最后一个执行的触发器，中间其它触发器的顺序无法保证</p>
<p>同类型的触发器不会导致递归(不会出现一个触发器执行之后调用自身)</p>
<p>定义触发器的时候的规则</p>
<ul>
<li>CREATE TRIGGER 必须是批处理中的第一个语句</li>
<li>如果表有外键定义了级联 DELETE/UPDATE 操作(<code>CASCADE</code>)那么无法在此表定义 DELETE/UPDATE 触发器</li>
</ul>
<h4 id="删除触发器">删除触发器</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">TRIGGER</span> triggerName</span><br></pre></td></tr></table></figure>
<h2 id="事务管理语言">事务管理语言</h2>
<h3 id="事务">事务</h3>
<h4 id="事务的属性-原子性一致性隔离性持久性">事务的属性: 原子性、一致性、隔离性、持久性</h4>
<p>原子性: 保证一个整体 一致性: 事务执行前数据一致、执行后也一致 隔离性: 事务之间互相不影响。事务完成之前，对数据库的修改对于其他事务不可见 持久性: 事务完成后，它对系统的影响是永久的</p>
<h4 id="事务模式">事务模式</h4>
<p>显式事务，隐式事务，自动事务模式</p>
<h4 id="启动事务语句">启动事务语句</h4>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">BEGIN</span> <span class="keyword">TRANSACTION</span> transaction_name</span><br><span class="line"></span><br><span class="line"><span class="keyword">COMMIT</span> <span class="keyword">TRANSACTION</span> transaction_name</span><br></pre></td></tr></table></figure>
<p>transaction_name 既可以用常量也可以使用变量</p>
<h4 id="事务回滚rollback">事务回滚(ROLLBACK)</h4>
<p>一旦出错，进行回滚，恢复到事务执行之前的状态</p>
<p>使用 ROLLBACK 通常结合 IF 语句来写(若检测到事务无法继续的因素，则进行回滚处理)</p>
<p>ROLLBACK语法格式:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ROLLBACK</span> <span class="keyword">TRANSACTION</span> transaction_name</span><br></pre></td></tr></table></figure>
<p>当T-SQL 语句产生运行时错误时，让 SQL SERVER 自动回滚当前事务: <code>SET XACT_ABORT ON</code> (不自动回滚为<code>SET XACT_ABORT OFF</code> 指的是回滚当前出错命令，但是该事务中已经执行的其它命令不会被回滚)</p>
<p>事务由大到小: 任务-进程-线程 线程是操作系统执行的最小单位</p>
<h2 id="数据控制语言">数据控制语言</h2>
<p>角色与权限管理</p>
<p>例子：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">EXEC sp_addrole 'customer_role'</span><br><span class="line">EXEC sp_addrole 'employee_role'</span><br><span class="line">EXEC sp_addrole 'manager_role'</span><br><span class="line"></span><br><span class="line"><span class="keyword">GRANT</span> <span class="keyword">SELECT</span>, <span class="keyword">INSERT</span>, <span class="keyword">UPDATE</span> <span class="keyword">ON</span> Customer <span class="keyword">TO</span> customer_role</span><br><span class="line"><span class="keyword">GRANT</span> <span class="keyword">SELECT</span>, <span class="keyword">INSERT</span>, <span class="keyword">UPDATE</span> <span class="keyword">ON</span> Orders <span class="keyword">TO</span> customer_role</span><br><span class="line"><span class="keyword">GRANT</span> <span class="keyword">SELECT</span>, <span class="keyword">INSERT</span>, <span class="keyword">UPDATE</span> <span class="keyword">ON</span> Orders_content <span class="keyword">TO</span> customer_role</span><br><span class="line"></span><br><span class="line"><span class="keyword">GRANT</span> <span class="keyword">EXECUTE</span> <span class="keyword">ON</span> add_to_cart <span class="keyword">TO</span> customer_role</span><br><span class="line"><span class="keyword">GRANT</span> <span class="keyword">EXECUTE</span> <span class="keyword">ON</span> check_is_ordering <span class="keyword">TO</span> customer_role</span><br></pre></td></tr></table></figure>
<h3 id="登录账号和数据库用户">登录账号和数据库用户</h3>
<p>登录账号在服务器级创建，数据库用户在数据库级创建</p>
<p>数据库用户总是与某一登录账号相关联，一个登录账号可以关联多个数据库用户（一对多）</p>
<h3 id="权限">权限</h3>
<h4 id="权限的种类">权限的种类</h4>
<ol type="1">
<li>对象权限</li>
<li>语句权限</li>
<li>隐含权限</li>
</ol>
<h4 id="权限管理">权限管理</h4>
<p><code>GRANT</code>赋予权限</p>
<p><code>DENY</code>禁止权限</p>
<p><code>REVOKE</code>收回已赋予的权限</p>
<h3 id="角色">角色</h3>
<h4 id="角色的分类">角色的分类</h4>
<ul>
<li>固定角色
<ul>
<li>固定服务器角色</li>
<li>固定数据库角色</li>
</ul></li>
<li>用户定义数据库角色</li>
</ul>
<h2 id="数据库的备份与恢复">数据库的备份与恢复</h2>
<h3 id="备份">备份</h3>
<h3 id="恢复">恢复</h3>
<p>SQL Server的三种恢复模型：</p>
<ul>
<li>简单恢复模型：恢复到上次备份处</li>
<li>完全恢复模型：恢复到特定时间点</li>
<li>大容量日志记录恢复模型：介于简单恢复模型和完全恢复模型之间（在性能和日志占用空间方面找到平衡）</li>
</ul>

          
            <div class='article_footer'>
              
                
  
    
    



  

  
    
    



  

  
    
    

    <section class="widget copyright  desktop mobile">
        <div class='content'>
            
                <blockquote>
                    
                        
                                    <p>博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议。转载请保留原文链接和作者。</p>

                                        
                                            
                </blockquote>
                
        </div>
    </section>
  

  


              
            </div>
          
        </div>
        
          


  <section class='meta' id="footer-meta">
    <div class='new-meta-box'>
      
        
          <div class="new-meta-item date" itemprop="dateUpdated" datetime="2021-01-23T15:52:49+08:00">
  <a class='notlink'>
    <i class="fas fa-edit fa-fw" aria-hidden="true"></i>
    <p>更新于：2021年1月23日 15:52</p>
  </a>
</div>

        
      
        
          
  
  <div class="new-meta-item meta-tags"><a class="tag" href="/junblog/tags/SQL/" rel="nofollow"><i class="fas fa-hashtag fa-fw" aria-hidden="true"></i><p>SQL</p></a></div> <div class="new-meta-item meta-tags"><a class="tag" href="/junblog/tags/T-SQL/" rel="nofollow"><i class="fas fa-hashtag fa-fw" aria-hidden="true"></i><p>T-SQL</p></a></div> <div class="new-meta-item meta-tags"><a class="tag" href="/junblog/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/" rel="nofollow"><i class="fas fa-hashtag fa-fw" aria-hidden="true"></i><p>数据库</p></a></div>


        
      
        
          
  <div class="new-meta-item share -mob-share-list">
  <div class="-mob-share-list share-body">
    
      
        <a class="-mob-share-qq" title="" rel="external nofollow noopener noreferrer"
          
          href="http://connect.qq.com/widget/shareqq/index.html?url=https://heliumhe.gitee.io/2021/01/23/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%A6%82%E8%AE%BA%E5%A4%8D%E4%B9%A0/&title=数据库概论知识点总结 - Jun's Blog&summary=数据库系统的基本概念
从数据到数据库
数据
数据的概念
数据：数据的内容是信息；数据的表现形式是符号记录
数据的分类

按数据加工程度分：分为三次数据、二次数据、一次数据、零次数据（加工程度由高到低）
按结构化程度分：




类型
含义
本质
举例




结构化数据
直接可用关系型数据库存储和管理的数据
先有结构，后有数据
关系数据库中的数据


半结构化数据
经过一定的加工处理以后可以用关系数据库存储和管理的数据
先有数据，后有结构
HTML、XML


非结构化数据
无法用关系型数据库存储和管理的数据
没有（或难以发现）统一结构的数据
语音、图像文件


"
          
          >
          
            <img src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-assets/logo/128/qq.png">
          
        </a>
      
    
      
        <a class="-mob-share-qzone" title="" rel="external nofollow noopener noreferrer"
          
          href="https://sns.qzone.qq.com/cgi-bin/qzshare/cgi_qzshare_onekey?url=https://heliumhe.gitee.io/2021/01/23/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%A6%82%E8%AE%BA%E5%A4%8D%E4%B9%A0/&title=数据库概论知识点总结 - Jun's Blog&summary=数据库系统的基本概念
从数据到数据库
数据
数据的概念
数据：数据的内容是信息；数据的表现形式是符号记录
数据的分类

按数据加工程度分：分为三次数据、二次数据、一次数据、零次数据（加工程度由高到低）
按结构化程度分：




类型
含义
本质
举例




结构化数据
直接可用关系型数据库存储和管理的数据
先有结构，后有数据
关系数据库中的数据


半结构化数据
经过一定的加工处理以后可以用关系数据库存储和管理的数据
先有数据，后有结构
HTML、XML


非结构化数据
无法用关系型数据库存储和管理的数据
没有（或难以发现）统一结构的数据
语音、图像文件


"
          
          >
          
            <img src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-assets/logo/128/qzone.png">
          
        </a>
      
    
      
        <a class="-mob-share-weibo" title="" rel="external nofollow noopener noreferrer"
          
          href="http://service.weibo.com/share/share.php?url=https://heliumhe.gitee.io/2021/01/23/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%A6%82%E8%AE%BA%E5%A4%8D%E4%B9%A0/&title=数据库概论知识点总结 - Jun's Blog&summary=数据库系统的基本概念
从数据到数据库
数据
数据的概念
数据：数据的内容是信息；数据的表现形式是符号记录
数据的分类

按数据加工程度分：分为三次数据、二次数据、一次数据、零次数据（加工程度由高到低）
按结构化程度分：




类型
含义
本质
举例




结构化数据
直接可用关系型数据库存储和管理的数据
先有结构，后有数据
关系数据库中的数据


半结构化数据
经过一定的加工处理以后可以用关系数据库存储和管理的数据
先有数据，后有结构
HTML、XML


非结构化数据
无法用关系型数据库存储和管理的数据
没有（或难以发现）统一结构的数据
语音、图像文件


"
          
          >
          
            <img src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-assets/logo/128/weibo.png">
          
        </a>
      
    
      
        
        <div class='hoverbox'>
          <a><img src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-assets/logo/128/wechat.png"></a>
          <div class='target'>
            <img src="">
          </div>
        </div>
      
    
  </div>
</div>



        
      
    </div>
  </section>


        
        
          <div class="prev-next">
            
              <a class='prev' href='/junblog/2021/02/11/B002%20Classification%20-%20SGD/'>
                <p class='title'><i class="fas fa-chevron-left" aria-hidden="true"></i>B002 Classification - SGD Method</p>
                <p class='content'>What is a classification problem?
Imagine an elementary school student is given a batch of learn...</p>
              </a>
            
            
              <a class='next' href='/junblog/2021/01/20/hexo%E7%BD%91%E7%AB%99%E6%90%AD%E5%BB%BA%E6%95%99%E7%A8%8B%20-%20BY%20HJY/'>
                <p class='title'>Hexo网站搭建教程 - BY HJY<i class="fas fa-chevron-right" aria-hidden="true"></i></p>
                <p class='content'>写在前面
我们这里采用的是hexo工具来生成博客网站的方案, 我们简单介绍一下它的基本构架.
整个方案分为三大部分: 其一是博客本身, 其二是部署到服务器, 其三是多人协作.

a) 博...</p>
              </a>
            
          </div>
        
      </section>
    </article>
  

  
    <!-- 显示推荐文章和评论 -->



  <article class="post white-box reveal comments blur">
    <section class="article typo">
      <p ct><i class='fas fa-comments'></i> 评论</p>
      
        <p cst>读者们可以在此处进行交流</p>
      
      
      
      
      
      
        <section id="comments">
          <div id="valine_container" class="valine_thread">
            <i class="fas fa-cog fa-spin fa-fw fa-2x"></i>
          </div>
        </section>
      
      
    </section>
  </article>


  




<!-- 根据页面mathjax变量决定是否加载MathJax数学公式js -->



  <script>
    window.subData = {
      title: '数据库概论知识点总结',
      tools: true
    }
  </script>


</div>
<aside class='l_side'>
  
  
    
    



  <section class="widget toc-wrapper blur desktop mobile" id="toc-div" >
    
  <header>
    
      <i class="fas fa-list fa-fw" aria-hidden="true"></i><span class='name'>本文目录</span>
    
  </header>


    <div class='content'>
        <ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#数据库系统的基本概念"><span class="toc-number">1.</span> <span class="toc-text">数据库系统的基本概念</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#从数据到数据库"><span class="toc-number">1.1.</span> <span class="toc-text">从数据到数据库</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#数据"><span class="toc-number">1.1.1.</span> <span class="toc-text">数据</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#数据的概念"><span class="toc-number">1.1.1.1.</span> <span class="toc-text">数据的概念</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#数据的分类"><span class="toc-number">1.1.1.2.</span> <span class="toc-text">数据的分类</span></a></li></ol></li><li class="toc-item toc-level-4"><a class="toc-link" href="#数据库"><span class="toc-number">1.1.2.</span> <span class="toc-text">数据库</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#数据库系统"><span class="toc-number">1.1.3.</span> <span class="toc-text">数据库系统</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#数据模型"><span class="toc-number">1.1.4.</span> <span class="toc-text">数据模型</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#概念模型e-r图的设计"><span class="toc-number">1.1.5.</span> <span class="toc-text">概念模型：E-R图的设计</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#数据库系统的模式结构"><span class="toc-number">1.1.6.</span> <span class="toc-text">数据库系统的模式结构</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#t-sql常用操作语言的分类"><span class="toc-number">1.2.</span> <span class="toc-text">T-SQL常用操作语言的分类</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#数据定义语言"><span class="toc-number">2.</span> <span class="toc-text">数据定义语言</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#数据库的创建修改删除"><span class="toc-number">2.1.</span> <span class="toc-text">数据库的创建、修改、删除</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#数据库的创建"><span class="toc-number">2.1.1.</span> <span class="toc-text">数据库的创建</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#数据库的修改"><span class="toc-number">2.1.2.</span> <span class="toc-text">数据库的修改</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#数据库的删除"><span class="toc-number">2.1.3.</span> <span class="toc-text">数据库的删除</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#表的创建修改删除"><span class="toc-number">2.2.</span> <span class="toc-text">表的创建、修改、删除</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#表的创建"><span class="toc-number">2.2.1.</span> <span class="toc-text">表的创建</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#补充使用查询结果来创建新表select-into"><span class="toc-number">2.2.2.</span> <span class="toc-text">*补充：使用查询结果来创建新表：SELECT INTO</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#表的修改"><span class="toc-number">2.2.3.</span> <span class="toc-text">表的修改</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#表的删除"><span class="toc-number">2.2.4.</span> <span class="toc-text">表的删除</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#视图的创建修改删除"><span class="toc-number">2.3.</span> <span class="toc-text">视图的创建、修改、删除</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#何为视图"><span class="toc-number">2.3.1.</span> <span class="toc-text">何为视图？</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#创建视图"><span class="toc-number">2.3.2.</span> <span class="toc-text">创建视图</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#修改视图"><span class="toc-number">2.3.3.</span> <span class="toc-text">修改视图</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#删除视图"><span class="toc-number">2.3.4.</span> <span class="toc-text">删除视图</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#利用视图更新数据"><span class="toc-number">2.3.5.</span> <span class="toc-text">利用视图更新数据</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#索引的创建修改删除"><span class="toc-number">2.4.</span> <span class="toc-text">索引的创建、修改、删除</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#创建索引"><span class="toc-number">2.4.1.</span> <span class="toc-text">创建索引</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#删除索引"><span class="toc-number">2.4.2.</span> <span class="toc-text">删除索引</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#索引的分类"><span class="toc-number">2.4.3.</span> <span class="toc-text">索引的分类</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#使用规则默认值和约束保证数据完整性"><span class="toc-number">2.5.</span> <span class="toc-text">使用规则、默认值和约束保证数据完整性</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#规则"><span class="toc-number">2.5.1.</span> <span class="toc-text">规则</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#创建规则"><span class="toc-number">2.5.1.1.</span> <span class="toc-text">创建规则</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#绑定规则"><span class="toc-number">2.5.1.2.</span> <span class="toc-text">绑定规则</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#解绑规则"><span class="toc-number">2.5.1.3.</span> <span class="toc-text">解绑规则</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#删除规则"><span class="toc-number">2.5.1.4.</span> <span class="toc-text">删除规则</span></a></li></ol></li><li class="toc-item toc-level-4"><a class="toc-link" href="#默认值"><span class="toc-number">2.5.2.</span> <span class="toc-text">默认值</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#创建默认值"><span class="toc-number">2.5.2.1.</span> <span class="toc-text">创建默认值</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#绑定默认值"><span class="toc-number">2.5.2.2.</span> <span class="toc-text">绑定默认值</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#解绑默认值"><span class="toc-number">2.5.2.3.</span> <span class="toc-text">解绑默认值</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#删除默认值"><span class="toc-number">2.5.2.4.</span> <span class="toc-text">删除默认值</span></a></li></ol></li><li class="toc-item toc-level-4"><a class="toc-link" href="#约束"><span class="toc-number">2.5.3.</span> <span class="toc-text">约束</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#数据操作语言"><span class="toc-number">3.</span> <span class="toc-text">数据操作语言</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#数据的添加更新和删除"><span class="toc-number">3.1.</span> <span class="toc-text">数据的添加、更新和删除</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#数据的添加"><span class="toc-number">3.1.1.</span> <span class="toc-text">数据的添加</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#数据的更新"><span class="toc-number">3.1.2.</span> <span class="toc-text">数据的更新</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#数据的删除"><span class="toc-number">3.1.3.</span> <span class="toc-text">数据的删除</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#使用delete语句删除"><span class="toc-number">3.1.3.1.</span> <span class="toc-text">使用DELETE语句删除</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#使用truncate语句删除"><span class="toc-number">3.1.3.2.</span> <span class="toc-text">使用TRUNCATE语句删除</span></a></li></ol></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#数据查询"><span class="toc-number">3.2.</span> <span class="toc-text">数据查询</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#可编程性"><span class="toc-number">4.</span> <span class="toc-text">可编程性</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#变量及常量"><span class="toc-number">4.1.</span> <span class="toc-text">变量及常量</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#程序控制流语句"><span class="toc-number">4.2.</span> <span class="toc-text">程序控制流语句</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#函数"><span class="toc-number">4.3.</span> <span class="toc-text">函数</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#标量函数"><span class="toc-number">4.3.1.</span> <span class="toc-text">标量函数</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#内嵌表值函数"><span class="toc-number">4.3.2.</span> <span class="toc-text">内嵌表值函数</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#多语句表值函数"><span class="toc-number">4.3.3.</span> <span class="toc-text">多语句表值函数</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#执行用户自定义函数"><span class="toc-number">4.3.4.</span> <span class="toc-text">执行用户自定义函数</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#修改删除函数"><span class="toc-number">4.3.5.</span> <span class="toc-text">修改、删除函数</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#游标"><span class="toc-number">4.4.</span> <span class="toc-text">游标</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#游标的基本使用"><span class="toc-number">4.4.1.</span> <span class="toc-text">游标的基本使用</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#进一步使用游标"><span class="toc-number">4.4.2.</span> <span class="toc-text">进一步使用游标</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#使用游标函数"><span class="toc-number">4.4.2.1.</span> <span class="toc-text">使用游标函数</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#用游标读取数据的一般方式"><span class="toc-number">4.4.2.2.</span> <span class="toc-text">用游标读取数据的一般方式</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#使用变量作为游标名游标变量"><span class="toc-number">4.4.2.3.</span> <span class="toc-text">使用变量作为游标名(游标变量)</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#滚动游标的控制"><span class="toc-number">4.4.2.4.</span> <span class="toc-text">滚动游标的控制</span></a></li></ol></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#存储过程"><span class="toc-number">4.5.</span> <span class="toc-text">存储过程</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#创建存储过程"><span class="toc-number">4.5.1.</span> <span class="toc-text">创建存储过程</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#执行存储过程"><span class="toc-number">4.5.2.</span> <span class="toc-text">执行存储过程</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#触发器"><span class="toc-number">4.6.</span> <span class="toc-text">触发器</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#多个触发器的执行顺序"><span class="toc-number">4.6.1.</span> <span class="toc-text">多个触发器的执行顺序</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#删除触发器"><span class="toc-number">4.6.2.</span> <span class="toc-text">删除触发器</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#事务管理语言"><span class="toc-number">5.</span> <span class="toc-text">事务管理语言</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#事务"><span class="toc-number">5.1.</span> <span class="toc-text">事务</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#事务的属性-原子性一致性隔离性持久性"><span class="toc-number">5.1.1.</span> <span class="toc-text">事务的属性: 原子性、一致性、隔离性、持久性</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#事务模式"><span class="toc-number">5.1.2.</span> <span class="toc-text">事务模式</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#启动事务语句"><span class="toc-number">5.1.3.</span> <span class="toc-text">启动事务语句</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#事务回滚rollback"><span class="toc-number">5.1.4.</span> <span class="toc-text">事务回滚(ROLLBACK)</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#数据控制语言"><span class="toc-number">6.</span> <span class="toc-text">数据控制语言</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#登录账号和数据库用户"><span class="toc-number">6.1.</span> <span class="toc-text">登录账号和数据库用户</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#权限"><span class="toc-number">6.2.</span> <span class="toc-text">权限</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#权限的种类"><span class="toc-number">6.2.1.</span> <span class="toc-text">权限的种类</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#权限管理"><span class="toc-number">6.2.2.</span> <span class="toc-text">权限管理</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#角色"><span class="toc-number">6.3.</span> <span class="toc-text">角色</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#角色的分类"><span class="toc-number">6.3.1.</span> <span class="toc-text">角色的分类</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#数据库的备份与恢复"><span class="toc-number">7.</span> <span class="toc-text">数据库的备份与恢复</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#备份"><span class="toc-number">7.1.</span> <span class="toc-text">备份</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#恢复"><span class="toc-number">7.2.</span> <span class="toc-text">恢复</span></a></li></ol></li></ol>
    </div>
  </section>


  


</aside>


  
  <footer class="clearfix">
    <br><br>
    
      
        <div class="aplayer-container">
          


        </div>
      
    
      
        <br>
        <div class="social-wrapper">
          
            
              <a href="/junblog/atom.xml"
                class="social fas fa-rss flat-btn"
                target="_blank"
                rel="external nofollow noopener noreferrer">
              </a>
            
          
            
              <a href="mailto:hejunyuan02@126.com"
                class="social fas fa-envelope flat-btn"
                target="_blank"
                rel="external nofollow noopener noreferrer">
              </a>
            
          
            
              <a href="https://music.163.com/#/user/home?id=63035382"
                class="social fas fa-headphones-alt flat-btn"
                target="_blank"
                rel="external nofollow noopener noreferrer">
              </a>
            
          
        </div>
      
    
      
        本站使用
        <a href="https://volantis.js.org/" target="_blank" class="codename">Volantis</a>
        作为主题，总访问量为
          <span id="busuanzi_value_site_pv"><i class="fas fa-circle-notch fa-spin fa-fw" aria-hidden="true"></i></span>
          次
        
      
    
      
        <div class='copyright'>
        <p><a href="https://junyuanhe.github.io/" target="_blank" rel="noopener">Copyright © 2019-2020 Jun</a></p>

        </div>
      
    
  </footer>

<script>setLoadingBarProgress(80);</script>


      <script>setLoadingBarProgress(60);</script>
    </div>
    <a class="s-top fas fa-arrow-up fa-fw" href='javascript:void(0)'></a>
  </div>
  
<script src="https://cdn.jsdelivr.net/npm/jquery@3.4/dist/jquery.min.js"></script>


  <script>
    
    var SEARCH_SERVICE = "hexo" || "hexo";
    var ROOT = "/junblog/" || "/";
    if (!ROOT.endsWith('/')) ROOT += '/';
  </script>





  <script async src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-volantis@2/js/instant_page.js" type="module" defer integrity="sha384-OeDn4XE77tdHo8pGtE1apMPmAipjoxUQ++eeJa6EtJCfHlvijigWiJpD7VDPWXV1"></script>


  <script src="https://cdn.jsdelivr.net/npm/scrollreveal@4.0.6/dist/scrollreveal.min.js"></script>
  <script type="text/javascript">
    $(function() {
      ScrollReveal().reveal('.l_main .reveal', {
        distance: '8px',
        duration: '800',
        interval: '100',
        scale: '1'
      });
    });
  </script>


  
<script src="https://cdn.jsdelivr.net/npm/node-waves@0.7.6/dist/waves.min.js"></script>

  <script type="text/javascript">
    $(function() {
      Waves.attach('.flat-btn', ['waves-button']);
      Waves.attach('.float-btn', ['waves-button', 'waves-float']);
      Waves.attach('.float-btn-light', ['waves-button', 'waves-float', 'waves-light']);
      Waves.attach('.flat-box', ['waves-block']);
      Waves.attach('.float-box', ['waves-block', 'waves-float']);
      Waves.attach('.waves-image');
      Waves.init();
    });
  </script>


  <script defer src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-busuanzi@2.3/js/busuanzi.pure.mini.js"></script>



  
  
  
    
<script src="https://cdn.jsdelivr.net/npm/jquery-backstretch@2.1.18/jquery.backstretch.min.js"></script>

    <script type="text/javascript">
      $(function(){
        var imgs=["https://cdn.jsdelivr.net/gh/xaoxuu/cdn-wallpaper/abstract/41F215B9-261F-48B4-80B5-4E86E165259E.jpeg", "https://cdn.jsdelivr.net/gh/xaoxuu/cdn-wallpaper/abstract/BBC19066-E176-47C2-9D22-48C81EE5DF6B.jpeg", "https://cdn.jsdelivr.net/gh/xaoxuu/cdn-wallpaper/abstract/B18FCBB3-67FD-48CC-B4F3-457BA145F17A.jpeg", "https://cdn.jsdelivr.net/gh/xaoxuu/cdn-wallpaper/abstract/67239FBB-E15D-4F4F-8EE8-0F1C9F3C4E7C.jpeg"];
        if ('true' == 'true') {
          function shuffle(arr){
            /*From countercurrent-time*/
            var n = arr.length;
            while(n--) {
              var index = Math.floor(Math.random() * n);
              var temp = arr[index];
              arr[index] = arr[n];
              arr[n] = temp;
            }
          }
          shuffle(imgs);
        }
        if ('.cover') {
          $('.cover').backstretch(
            imgs,
          {
            duration: "20000",
            fade: "1500"
          });
        } else {
          $.backstretch(
            imgs,
          {
            duration: "20000",
            fade: "1500"
          });
        }
      });
    </script>
  











  
    
<script src="/junblog/js/valine.js"></script>

  
  <script>
  var GUEST_INFO = ['nick','mail','link'];
  var meta = 'nick,mail'.split(',').filter(function(item){
    return GUEST_INFO.indexOf(item) > -1
  });
  var REQUIRED_FIELDS = ['nick','mail','link'];
  var requiredFields = 'nick,mail'.split(',').filter(function(item){
    return REQUIRED_FIELDS.indexOf(item) > -1
  });
  var valine = new Valine();
  function emoji(path, idx, ext) {
      return path + "/" + path + "-" + idx + "." + ext;
  }
  var emojiMaps = {};
  for (var i = 1; i <= 54; i++) {
    emojiMaps['tieba-' + i] = emoji('tieba', i, 'png');
  }
  for (var i = 1; i <= 101; i++) {
    emojiMaps['qq-' + i] = emoji('qq', i, 'gif');
  }
  for (var i = 1; i <= 116; i++) {
    emojiMaps['aru-' + i] = emoji('aru', i, 'gif');
  }
  for (var i = 1; i <= 125; i++) {
    emojiMaps['twemoji-' + i] = emoji('twemoji', i, 'png');
  }
  for (var i = 1; i <= 4; i++) {
    emojiMaps['weibo-' + i] = emoji('weibo', i, 'png');
  }
  valine.init({
    el: '#valine_container',
    meta: meta,
    
    appId: "GbPm6UIzHrGduLaANgBIdhkf-gzGzoHsz",
    appKey: "SFuacIL4IJSnGk9TDX46eb0Y",
    placeholder: "快来评论吧~",
    pageSize:'10',
    avatar:'robohash',
    lang:'zh-cn',
    visitor: 'true',
    highlight: 'true',
    mathJax: 'true',
    enableQQ: 'true',
    requiredFields: requiredFields,
    emojiCDN: 'https://cdn.jsdelivr.net/gh/xaoxuu/cdn-assets/emoji/valine/',
    emojiMaps: emojiMaps
  })
  </script>





  
<script src="/junblog/js/app.js"></script>



  
<script src="https://cdn.jsdelivr.net/gh/xaoxuu/cdn-volantis@2.6.5/js/search.js"></script>







<!-- 复制 -->

  <script src="https://cdn.jsdelivr.net/npm/clipboard@2/dist/clipboard.min.js"></script>
<script>
  function wait(callback, seconds) {
    var timelag = null;
    timelag = window.setTimeout(callback, seconds);
  }
  !function (e, t, a) {
    var initCopyCode = function(){
      var copyHtml = '';
      copyHtml += '<button class="btn-copy" data-clipboard-snippet="">';
      copyHtml += '<i class="fas fa-copy"></i><span>COPY</span>';
      copyHtml += '</button>';
      $(".highlight .code pre").before(copyHtml);
      $(".article pre code").before(copyHtml);
      var clipboard = new ClipboardJS('.btn-copy', {
        target: function(trigger) {
          return trigger.nextElementSibling;
        }
      });
      clipboard.on('success', function(e) {
        let $btn = $(e.trigger);
        $btn.addClass('copied');
        let $icon = $($btn.find('i'));
        $icon.removeClass('fa-copy');
        $icon.addClass('fa-check-circle');
        let $span = $($btn.find('span'));
        $span[0].innerText = 'COPIED';
        
        wait(function () { // 等待两秒钟后恢复
          $icon.removeClass('fa-check-circle');
          $icon.addClass('fa-copy');
          $span[0].innerText = 'COPY';
        }, 2000);
      });
      clipboard.on('error', function(e) {
        e.clearSelection();
        let $btn = $(e.trigger);
        $btn.addClass('copy-failed');
        let $icon = $($btn.find('i'));
        $icon.removeClass('fa-copy');
        $icon.addClass('fa-times-circle');
        let $span = $($btn.find('span'));
        $span[0].innerText = 'COPY FAILED';
        
        wait(function () { // 等待两秒钟后恢复
          $icon.removeClass('fa-times-circle');
          $icon.addClass('fa-copy');
          $span[0].innerText = 'COPY';
        }, 2000);
      });
    }
    initCopyCode();
  }(window, document);
</script>




<!-- fancybox -->
<script src="https://cdn.jsdelivr.net/gh/fancyapps/fancybox@3.5.7/dist/jquery.fancybox.min.js"></script>
<script>
  function pjax_fancybox() {
    $(".article-entry").find("img").not('.inline').not('a img').each(function () { //渲染 fancybox
      var element = document.createElement("a"); // a 标签
      $(element).attr("pjax-fancybox", "");  // 过滤 pjax
      $(element).attr("href", $(this).attr("src"));
      if ($(this).attr("data-original")) {
        $(element).attr("href", $(this).attr("data-original"));
      }
      $(element).attr("data-fancybox", "images");
      var caption = "";   // 描述信息
      if ($(this).attr('alt')) {  // 标准 markdown 描述信息
        $(element).attr('data-caption', $(this).attr('alt'));
        caption = $(this).attr('alt');
      }
      var div = document.createElement("div");
      $(div).addClass("fancybox");
      $(this).wrap(div); // 最外层套 div ，其实主要作用还是 class 样式
      var span = document.createElement("span");
      $(span).addClass("image-caption");
      $(span).text(caption); // 加描述
      $(this).after(span);  // 再套一层描述
      $(this).wrap(element);  // 最后套 a 标签
    })
    $(".article-entry").find("img").fancybox({
      selector: '[data-fancybox="images"]',
      hash: false,
      loop: false,
      closeClick: true,
      helpers: {
        overlay: {closeClick: true}
      },
      buttons: [
        "zoom",
        "close"
      ]
    });
  };
  $(function () {
    pjax_fancybox();
  });
</script>





  <script>setLoadingBarProgress(100);</script>
</body>
</html>
